package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.KaoQin;
import com.zr.util.JDBCUtil;

import sun.tools.jar.Main;

public class KaoQinSelectDao{
	/**
	 * 查询所有考勤的信息  分页显示
	 * @param start  起始的位置
	 * @param rows   分页的记录数
	 * @return
	 */
	public List<KaoQin>  findAllWorker(int start,int rows){
		List<KaoQin>  kqs = new ArrayList<KaoQin>();
		String sql = "select * from kaoqin limit ?,?";
		Connection  con =  JDBCUtil.getDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			pst.setInt(1,start);
			pst.setInt(2,rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				KaoQin  kq = new KaoQin(rs.getInt("kid"), rs.getString("wname"), rs.getString("wsex"), rs.getInt("vacate_day"), rs.getString("start_time"), rs.getString("end_time"));
				kqs.add(kq);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  kqs;
	}
	/**
	 * 查询所有考勤信息总数
	 * @return  所有考勤信息总数
	 */
	public   int  getAllWorker(){
		int scount = 0;
		String sql =  "select count(*) scount from  kaoqin ";
		Connection  con = JDBCUtil.getDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return scount;
	}
	/**
	 * 删除考勤信息
	 * @param kid 考勤ID
	 * @return    0表示删除失败，1表示删除成功
	 */
	public int  delKaoQinByKid(int kid){
		int temp = 0;
		String sql = "delete from kaoqin where kid=?";
		Connection  con =  JDBCUtil.getDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			pst.setInt(1, kid);
			int temp1 = pst.executeUpdate();
			if(temp1>0){
				temp = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  temp;
	}
	/**
	 * 增加考勤信息
	 * @param wname	员工姓名
	 * @param wsex	员工性别
	 * @param vacate_day	员工请假天数
	 * @param start_time	员工上班时间
	 * @param end_time		员工下班时间
	 * @return	0表示添加失败，1表示添加成功
	 */	
	public int  addKaoQin(String wname,String wsex,int vacate_day,String start_time,String end_time){
		int temp = 0;
		String sql = "INSERT INTO kaoqin (wname,wsex,vacate_day,start_time,end_time) VALUES (?, ?, ?, ?, ?);";
		Connection  con =  JDBCUtil.getDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			pst.setString(1, wname);
			pst.setString(2, wsex);
			pst.setInt(3, vacate_day);
			pst.setString(4, start_time);
			pst.setString(5, end_time);
			int temp1 = pst.executeUpdate();
			if(temp1>0){
				temp = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  temp;
	}
	 /**
     * 对数据库中worker是否拥有该用户
     * @param wname 传入用户名字
     * @return  有返回true
     */
    public List<Object> validataWname(String wname){
    	String wsex="";
    	List<Object>  kqs = new ArrayList<Object>();
    	String sql="select wsex from worker where wname=?";
    	Connection conn=JDBCUtil.getDBConnection();
    	try {
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setString(1, wname);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){			
				kqs.add(rs.getString("wsex"));
			}else{
				wsex="null";
				kqs.add(wsex);
			}
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return kqs;
    } 

   /**
    * 通过员工姓名查询需要的考勤信息  分页显示
    * @param wname	查询的员工姓名
    * @param start	起始的位置
    * @param rows	分页的记录数
    * @return
    */
	public List<KaoQin>  findOnePersonKaoqin(String wname,int start,int rows){
		List<KaoQin>  kqs = new ArrayList<KaoQin>();
		String sql = "select * from kaoqin where wname LIKE ? limit ?,?";
		Connection  con =  JDBCUtil.getDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			pst.setString(1, wname+"%");
			pst.setInt(2, start);
			pst.setInt(3, rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				KaoQin  kq = new KaoQin(rs.getInt("kid"), rs.getString("wname"), rs.getString("wsex"), rs.getInt("vacate_day"), rs.getString("start_time"), rs.getString("end_time"));
				kqs.add(kq);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  kqs;
	}
	/**
	 * 通过与员工姓名查询考勤信息总数
	 * @param wname	员工姓名
	 * @return	员工考勤总数
	 */
	public   int  findOnePersonKaoqinCount(String wname){
		int scount = 0;
		String sql =  "select count(*) scount from  kaoqin where wname LIKE ?";
		Connection  con = JDBCUtil.getDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql);
			pst.setString(1, wname+"%");
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return scount;		
	}
	/**
	    * 通过时间查询需要的考勤信息  分页显示
	    * @param datatime	查询的员工姓名
	    * @param start	起始的位置
	    * @param rows	分页的记录数
	    * @return
	    */
		public List<KaoQin>  findTimeKaoqin(String datatime,int start,int rows){
			List<KaoQin>  kqs = new ArrayList<KaoQin>();
			String sql = "select * from kaoqin where start_time LIKE ? limit ?,?";
			Connection  con =  JDBCUtil.getDBConnection();
			try {
				PreparedStatement  pst = con.prepareStatement(sql);
				pst.setString(1, datatime+"%");
				pst.setInt(2, start);
				pst.setInt(3, rows);
				ResultSet  rs = pst.executeQuery();
				while(rs.next()){
					KaoQin  kq = new KaoQin(rs.getInt("kid"), rs.getString("wname"), rs.getString("wsex"), rs.getInt("vacate_day"), rs.getString("start_time"), rs.getString("end_time"));
					kqs.add(kq);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				JDBCUtil.closedConnection(con);
			}
			return  kqs;
		}
		/**
		 * 通过与时间查询考勤信息总数
		 * @param wname	员工姓名
		 * @return	员工考勤总数
		 */
		public   int  findTimeKaoqinCount(String datatime){
			int scount = 0;
			String sql =  "select count(*) scount from  kaoqin where start_time LIKE ?";
			Connection  con = JDBCUtil.getDBConnection();
			try {
				PreparedStatement  pst = con.prepareStatement(sql);
				pst.setString(1, datatime+"%");
				ResultSet  rs = pst.executeQuery();
				if(rs.next()){
					scount = rs.getInt("scount");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				JDBCUtil.closedConnection(con);
			}
			return scount;		
		}
		/**
		    * 通过时间和姓名查询需要的考勤信息  分页显示
		    * @param datatime	查询的员工姓名
		    * @param start	起始的位置
		    * @param rows	分页的记录数
		    * @return
		    */
			public List<KaoQin>  findTimeAndNameKaoqin(String wname,String datatime,int start,int rows){
				List<KaoQin>  kqs = new ArrayList<KaoQin>();
				String sql = "select * from kaoqin where wname LIKE ? and start_time LIKE ? limit ?,?";
				Connection  con =  JDBCUtil.getDBConnection();
				try {
					PreparedStatement  pst = con.prepareStatement(sql);
					pst.setString(1, wname+"%");
					pst.setString(2, datatime+"%");
					pst.setInt(3, start);
					pst.setInt(4, rows);
					ResultSet  rs = pst.executeQuery();
					while(rs.next()){
						KaoQin  kq = new KaoQin(rs.getInt("kid"), rs.getString("wname"), rs.getString("wsex"), rs.getInt("vacate_day"), rs.getString("start_time"), rs.getString("end_time"));
						kqs.add(kq);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				}
				return  kqs;
			}
			/**
			 * 通过与时间查询考勤信息总数
			 * @param wname	员工姓名
			 * @param datatime	时间
			 * @return	员工考勤总数
			 */
			public   int  findTimeAndNameKaoqinCount(String wname,String datatime){
				int scount = 0;
				String sql =  "select count(*) scount from  kaoqin where wname LIKE ? and start_time LIKE ?";
				Connection  con = JDBCUtil.getDBConnection();
				try {
					PreparedStatement  pst = con.prepareStatement(sql);
					pst.setString(1, wname+"%");
					pst.setString(2, datatime+"%");
					ResultSet  rs = pst.executeQuery();
					if(rs.next()){
						scount = rs.getInt("scount");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				}
				return scount;		
			}
			/**
			 * 修改考勤信息
			 * @param kid 考勤ID
			 * @return    0表示删除失败，1表示删除成功
			 */
			public int  updateKaoQinByKid(int kid,int vacate_day,String start_time,String end_time){
				int temp = 0;
				String sql = "update kaoqin set vacate_day=?,start_time=?,end_time=? where kid=?";
				Connection  con =  JDBCUtil.getDBConnection();
				try {
					PreparedStatement  pst = con.prepareStatement(sql);
					pst.setInt(1, vacate_day);
					pst.setString(2, start_time);
					pst.setString(3, end_time);
					pst.setInt(4, kid);
					int temp1 = pst.executeUpdate();
					if(temp1>0){
						temp = 1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				}
				return  temp;
			}
			
}
